<?php
$page_type = "S";
require_once 'header.php';

$cid = "";
$cid = $_SESSION['user_id'];

echo("<br><h2>Personal Information</h2>");

//Query personal information
$sql = "select sid, fname, lname, dob, street, city, state, zip, stflag, pflag from CLIENT where client_id = '$cid'";
$result_array = execute_sql_in_oracle($sql);
$result = $result_array["flag"];
$cursor = $result_array["cursor"];
if(!$result){
  display_oracle_error_message($cursor);
  die("Profile query failed.");
}
$values = oci_fetch_array($cursor);
$sid = $values[0];
$fname = $values[1];
$lname = $values[2];
$dob = $values[3];
$street = $values[4];
$city = $values[5];
$state = $values[6];
$zip = $values[7];
$stflag = $values[8];
$pflag = $values[9];
oci_free_statement($cursor);

$name = $fname . " " . $lname;
$age = floor( (strtotime('now') - strtotime($dob)) / 31556926);
$address = $street . ", " . $city . ", " . $state . " " . $zip;
$student_type = "";
$status = "";
if($stflag == 0)
  $student_type = "Undergraduate";
else
  $student_type = "Graduate";
if($pflag == 0)
  $status = "Non Probational";
else
  $status = "Probational";

echo("<table> " .
 "<tr><td>Student ID: </td> <td>$sid</td></tr>" .
 "<tr><td>Name: </td> <td>$name</td></tr>" .
 "<tr><td>Age: </td> <td>$age</td></tr>" .
 "<tr><td>Address: </td><td>$address</td></tr>" .
 "<tr><td>Student Type: </td><td>$student_type</td></tr>" .
 "<tr><td>Status: </td> <td>$status</td></tr>" .
 "</table>");

//Generate Course History
echo("<br><h2>Course Record</h2>" . 
 "<table border = '1'><caption><h3>Course(s) History</h3></caption>" .
 "<tr> <th> Course ID </th> <th> Course Number </th> <th> Title </th> <th> Semester </th> <th> Credit </th> <th> Grade </th> </tr> ");

$sql = "select t.seqid, co.cno, cd.title, co.semester, cd.credit, t.grade from TAKEN t, COURSE_OFFERING co, COURSE_DESCRIPTION cd where t.sid = '$sid' and t.seqid = co.seqid and co.cno = cd.cno";
$result_array = execute_sql_in_oracle($sql);
$result = $result_array["flag"];
$cursor = $result_array["cursor"];
if (!$result) {
  display_oracle_error_message($cursor);
  die("Course History query failed.");
}

while($values = oci_fetch_array($cursor)) {
  $t_seqid = $values[0];
  $t_cno = $values[1];
  $t_title = $values[2];
  $t_semester = $values[3];
  $t_credit = $values[4];
  $t_grade = $values[5];

  echo("<tr><td>$t_seqid</td><td>$t_cno</td><td>$t_title</td><td>$t_semester</td><td>$t_credit</td><td>$t_grade</td></tr>");
}
echo("</table><br>");

//Generate Transcript Totals table
echo("<table border = '1'><caption><h3>Transcript Totals</h3></caption>" .
 "<tr> <th>Completed Courses</th><th>Earned Credit Hours</th><th>GPA</th></tr>");

//Create the view to compute GPA
$sql = "create or replace view AVG_GRADE(avg_grade) as".
 " select (sum(t.grade * cd.credit)/sum(cd.credit))".
 " from TAKEN t, COURSE_OFFERING co, COURSE_DESCRIPTION cd".
 " where t.sid = '$sid' and t.grade >0 and t.seqid = co.seqid and co.cno = cd.cno";
execute_sql_in_oracle($sql);

//Query the content of Transcript Totals
$sql = "select count(*), sum(cd.credit), avg_grade".
 " from TAKEN t, COURSE_OFFERING co, COURSE_DESCRIPTION cd, AVG_GRADE".
 " where t.sid = '$sid' and t.seqid = co.seqid and co.cno = cd.cno".
 " group by avg_grade";
$result_array = execute_sql_in_oracle($sql);
$result = $result_array["flag"];
$cursor = $result_array["cursor"];
if (!$result) {
  display_oracle_error_message($cursor);
  die("Transcript totals query failure");
}
if($values = oci_fetch_array($cursor)){
  $course_total = $values[0];
  $credit_total = $values[1];
  $avg_grade = $values[2];
}
else {
  $course_total = "";
  $credit_total = "";
  $avg_grade = "";
}

//Compute $gpa from $avg_grade
$gpa = "";
$gpa = ($avg_grade - 50)/10;
if($gpa < 0)
  $gpa = 0.0;
if($gpa > 4)
  $gpa = 4.0;

//Generate the Transcript Totals table
echo("<tr> <td>$course_total</td><td>$credit_total</td><td>$gpa</td> </tr></table>");
oci_free_statement($cursor);

//Generate Course Offerings table
echo("<br><table border = '1'><caption><h3>General Course Information</h3></caption>" .
 "<tr> <th>Course ID</th><th>Course Number</th><th>Title</th><th>Description</th><th>Credits</th><th>Semester</th><th>Time</th></tr>");

//Generate Course Offerings
$sql = "select co.seqid, co.cno, cd.title, cd.description, cd.credit, co.semester, co.time ".
 " from COURSE_OFFERING co, COURSE_DESCRIPTION cd where co.cno = cd.cno";
$result_array = execute_sql_in_oracle($sql);
$result = $result_array["flag"];
$cursor = $result_array["cursor"];
if (!$result) {
  display_oracle_error_message($cursor);
  die("Course Offerings query failure");
}

while($values = oci_fetch_array($cursor)){
  $i_seqid = "";
  $i_cno = "";
  $i_title = "";
  $i_description = "";
  $i_credit = "";
  $i_semester = "";
  $i_time = "";

  $i_seqid = $values[0];
  $i_cno = $values[1];
  $i_title = $values[2];
  $i_description = $values[3];
  $i_credit = $values[4];
  $i_semester = $values[5];
  $i_time = $values[6];

  echo("<tr> <td>$i_seqid</td><td>$i_cno</td><td>$i_title</td><td>$i_description</td><td>$i_credit</td><td>$i_semester</td><td>$i_time</td></tr>");

}
echo("</table>");
oci_free_statement($cursor);





?>
</body>
</html>